A portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent files or records, but otherwise reserves all copyright rights whatsoever. The following notice shall apply to this document: Copyright(copyright) 1999, Microsoft, Inc.
The present invention pertains generally to computer-implemented databases, and more particularly to representing virtual cubes in such databases.
Online analytical processing (OLAP) is a key part of most data warehouse and business analysis systems. OLAP services provide for fast analysis of multidimensional information. For this purpose, OLAP services provide for multidimensional access and navigation of data in an intuitive and natural way, providing a global view of data that can be drilled down into particular data of interest. Speed and response time are important attributes of OLAP services that allow users to browse and analyze data online in an efficient manner. Further, OLAP services typically provide analytical tools to rank, aggregate, and calculate lead and lag indicators for the data under analysis.
A fundamental entity that is present in typical OLAP databases is a cube. A cube is a multidimensional representation of a set of data having varying aspects. A cube comprises a set of dimensions and a set of measures. In this context, a dimension is a structural attribute of the cube that is a list of members of a similar type in the user""s perception of the data. Typically, there is a hierarchy associated with the dimension. For example, a time dimension can consist of days, weeks, months, and years, while a geography dimension can consist of cities, states/provinces, and countries. Dimension members act as indices for identifying a particular cell or range of cells within a multidimensional array.
A measure is a structural attribute of the cube that comprises a particular type of value that provides detail data for particular members within the dimensions. For example, sale amounts and units sold can be measures of a retail cube having a time dimension and a geography dimension. The measures provide the sale amounts and units sold for a particular geographic region at a particular point in time.
While cubes in OLAP systems represent a convenient and flexible mechanism for representing multidimensional data, several problems remain. First, current OLAP systems do not provide a mechanism for performing cross cube analysis. Cross cube analysis involves the analysis of measures from different cubes across similar dimensions. For example, consider a cube xe2x80x9cproductxe2x80x9d that contains product cost data over time, and a cube xe2x80x9cretailxe2x80x9d that has sales data over time. In this example, it is desirable to compare cost data with sales data, however in current systems the analysis must be performed in multiple stages because the source data resides in different cubes. The cost data must be extracted from the cube and held in temporary variable locations. The sales data must then be extracted from the retail cube. Finally, the data can be analyzed.
A second problem with the implementation of cubes in current systems is that there is little flexibility in defining security parameters for a cube. Current systems typically implement an all or none access scheme for the cube. That is, if a user has access to any cell in a cube, the user has access to all the cells in the cube. Given that cubes can have many dimensions and measures, some representing sensitive data and other dimensions having public data, all or none security is undesirable.
Thus there is a need in the art for a system that can simultaneously represent and present for analysis data from multiple cubes. In addition, there is a need for a system that provides data security by exposing only a subset of the cube to a user.
The above-mentioned shortcomings, disadvantages and problems are addressed by the present invention, which will be understood by reading and studying the following specification.
Systems and methods for creating and accessing virtual cubes are disclosed. A virtual cube is a logical representation of a subset of the dimensions and measures of physical cubes maintained by a server, such as an OLAP server. The virtual cube is defined by metadata that maps each dimension and measure of the virtual cube to a corresponding dimension or measure of a physical cube.
One aspect of the system is that the virtual cube is a union of a subset of the dimensions and measures of one or more physical cubes. The virtual cube does not contain any cell data itself, rather the mappings are used to access cell data maintained in the physical cubes contributing dimensions and measures to the virtual cube.
A further aspect of the system is that default members are created in dimensions that are not shared by the contributing physical cubes. The default members can be an automatically created summation of the members of the non-shared dimensions, or an administrator of the system can determine the default members. The default members provide a placeholder index for locating cells or ranges of cells in non-shared dimensions.
A still further aspect of the system is that a query processing component receives a request for a cell in the virtual cube and determines the physical cube cell that corresponds to the virtual cube cell. The query processing component uses the virtual cube metadata to identify the physical cube measure containing the required cell. The query processor then determines if the physical cube cell is in a local cache, of if the cell must be obtained from the OLAP server. After retrieving the physical cube cell from either the local cache or the OLAP server, the query processor returns the cell value to the requester as the value of the virtual cube cell.
The present invention describes systems, clients, servers, methods, and computer-readable media of varying scope. In addition to the aspects and advantages of the present invention described in this summary, further aspects and advantages of the invention will become apparent by reference to the drawings and by reading the detailed description that follows.